﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;
using PetaPoco.Internal;
using System.Data.SqlClient;

namespace PetaPoco
{
    public partial class Database
    {
        #region Private

        private T ExecuteAction<T>(Func<T> execFun)
        {
            OpenSharedConnection();
            try
            {
                return execFun();
            }
            finally
            {
                CloseSharedConnection();
            }
        }
        #endregion

        #region Publice Create


        public IDbDataAdapter CreateDataAdapter()
        {
            var dataAdapter = _factory.CreateDataAdapter();
            return dataAdapter;
        }

        public IDbDataParameter CreateDataParameter(string parameterName, object value, DbType dbType, int size, ParameterDirection direction)
        {
            var para = _factory.CreateParameter();
            para.ParameterName = parameterName;
            para.Value = value;
            para.Direction = direction;
            para.Size = size;
            para.DbType = dbType;
            return para;
        }
        #endregion

        #region StoredProcedure


        /// <summary>
        /// 执行存储过程，返回影响行数
        /// </summary>
        /// <param name="procedureName">存储过程名</param>
        /// <param name="paras">参数</param>
        /// <returns>影响行数</returns>
        public int StoredProcedure(string procedureName, params IDbDataParameter[] paras)
        {
            return ExecuteAction<int>(() =>
            {
                using (var cmd = CreateCommand(this.Connection, procedureName, paras))
                {
                    foreach (var para in paras)
                        cmd.Parameters.Add(para);
                    cmd.CommandType = CommandType.StoredProcedure;

                    return cmd.ExecuteNonQuery();
                }
            });
        }

        /// <summary>
        /// 执行存储过程，返回数据列表
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="procedureName">存储过程名称</param>
        /// <param name="paras">参数</param>
        /// <returns>返回列表</returns>
        public List<T> StoredProcedureFetch<T>(string procedureName, params IDbDataParameter[] paras)
        {
            return ExecuteAction<List<T>>(() =>
            {
                using (var cmd = CreateCommand(this.Connection, procedureName, paras))
                {
                    foreach (var para in paras) { cmd.Parameters.Add(para); }
                    cmd.CommandType = CommandType.StoredProcedure;

                    List<T> result = new List<T>();
                    var pd = PocoData.ForType(typeof(T));
                    try
                    {
                        using (IDataReader r = cmd.ExecuteReader())
                        {
                            OnExecutedCommand(cmd);
                            var factory = pd.GetFactory(cmd.CommandText, _sharedConnection.ConnectionString, 0, r.FieldCount, r) as Func<IDataReader, T>;
                            while (r.Read())
                            {
                                result.Add(factory(r));
                            }
                            return result;
                        }
                    }
                    catch (Exception x)
                    {
                        OnException(x);
                        throw;
                    }
                }
            });
        }

        //public void SqlBulkCopy<T>(IEnumerable<T> entities)
        //{
        //    var pd = PocoData.ForType(typeof(T));

        //    foreach (var entity in entities)
        //    {
        //        foreach (var key in pd.Columns.Keys)
        //        {
        //            var column = pd.Columns[key];

        //            if (column.ResultColumn) continue;

        //            //var columnType = column.PropertyInfo.PropertyType;

        //            //if (column.PropertyInfo.PropertyType.IsGenericType && column.PropertyInfo.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
        //            //{
        //            //    columnType = Nullable.GetUnderlyingType(column.PropertyInfo.PropertyType);
        //            //}
        //            //result.Columns.Add(new DataColumn(column.ColumnName, columnType));
        //        }
        //    }

        //    //using (SqlBulkCopy bulkCopy =
        //    //               new SqlBulkCopy(this.Connection as SqlConnection))
        //    //{
        //    //    bulkCopy.DestinationTableName = pd.TableInfo.TableName;

        //    //    bulkCopy.WriteToServer(tb);
        //    //}
        //    //MySqlBulkLoader bcp1 = new MySql.Data.MySqlClient.MySqlBulkLoader(this.Connection as MySqlConnection);
        //    //bcp1.TableName = pd.TableInfo.TableName;
        //    //bcp1.FieldTerminator = "\t";
        //    //bcp1.LineTerminator = "\r\n";
        //    //bcp1. = txtPath;
        //    //bcp1.NumberOfLinesToSkip = 0;
        //    //bcp1.Load();      
        //}

        #endregion

        #region Execute

        /// <summary>
        /// 执行不带参数语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>影响行</returns>
        public int ExecuteNoParamete(string sql)
        {
            try
            {
                OpenSharedConnection();
                try
                {
                    using (var cmd = _sharedConnection.CreateCommand())
                    {
                        cmd.Connection = _sharedConnection;
                        cmd.CommandText = sql;
                        cmd.Transaction = _transaction;
                        var retv = cmd.ExecuteNonQuery();
                        return retv;
                    }
                }
                finally
                {
                    CloseSharedConnection();
                }
            }
            catch (Exception x)
            {
                if (OnException(x))
                    throw;
                return -1;
            }
        }
        #endregion

        #region Table And Hashtable

        /// <summary>
        /// Get DataTable
        /// </summary>
        /// <param name="sql">query sql</param>
        /// <returns>table</returns>
        public DataTable Table(Sql sql)
        {
            return Table(sql.SQL, sql.Arguments);
        }

        /// <summary>
        /// Get DataTable
        /// </summary>
        /// <param name="sql">query sql</param>
        /// <param name="args">argument list</param>
        /// <returns>hashtables</returns>
        public DataTable Table(string sql, params object[] args)
        {
            return ExecuteAction<DataTable>(() =>
            {
                using (var cmd = CreateCommand(this.Connection, sql, args))
                {
                    var dat = CreateDataAdapter();
                    dat.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    dat.Fill(ds);
                    return ds.Tables.Count > 0 ? ds.Tables[0] : new DataTable();
                }
            });
        }

        /// <summary>
        /// Get Hashtables
        /// </summary>
        /// <param name="sql">query sql</param>
        /// <returns>hashtables</returns>
        public List<Hashtable> Hashtables(Sql sql)
        {
            return Hashtables(sql.SQL, sql.Arguments);
        }

        /// <summary>
        /// Get Hashtables
        /// </summary>
        /// <param name="sql">query sql</param>
        /// <param name="args">argument list</param>
        /// <returns>hashtables</returns>
        public List<Hashtable> Hashtables(string sql, params object[] args)
        {
            List<Hashtable> result = new List<Hashtable>();
            var dataTable = Table(sql, args);
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    Hashtable hashtable = new Hashtable();
                    foreach (DataColumn column in dataTable.Columns)
                    {
                        hashtable.Add(column.ColumnName, row[column]);
                    }
                    result.Add(hashtable);
                }
            }
            return result;
        }
        #endregion

        #region 将entities直接转DataTable

        /// <summary>
        /// 将entities直接转DataTable
        /// </summary>
        /// <typeparam name="T">Entity type</typeparam>
        /// <param name="entities">entity集合</param>
        /// <returns>将Entity的值转为DataTable</returns>
        public DataTable EntityToDataTable<T>(IEnumerable<T> entities)
        {
            var type = typeof(T);
            var result = GetEntityToDataTableSchema(type);
            if (entities.Count() == 0)
            {
                return result;
            }

            var pd = PocoData.ForType(type);
            foreach (var entity in entities)
            {
                var dr = result.NewRow();
                foreach (var key in pd.Columns.Keys)
                {
                    var column = pd.Columns[key];
                    if (column.ResultColumn) continue;

                    var columnValue = column.GetValue(entity);
                    if (columnValue == null) dr[column.ColumnName] = DBNull.Value;
                    else
                    {
                        dr[column.ColumnName] = columnValue;
                    }
                }
                result.Rows.Add(dr);
            }
            return result;
        }

        /// <summary>
        /// Gets the entity to data table schema.
        /// </summary>
        /// <param name="entityType">Type of the entity.</param>
        /// <returns>对应Entity属性的DataTable</returns>
        public DataTable GetEntityToDataTableSchema(Type entityType)
        {
            var pd = PocoData.ForType(entityType);

            var result = new DataTable(pd.TableInfo.TableName);
            foreach (var key in pd.Columns.Keys)
            {
                var column = pd.Columns[key];

                if (column.ResultColumn) continue;

                var columnType = column.PropertyInfo.PropertyType;

                if (column.PropertyInfo.PropertyType.IsGenericType && column.PropertyInfo.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                {
                    columnType = Nullable.GetUnderlyingType(column.PropertyInfo.PropertyType);
                }
                result.Columns.Add(new DataColumn(column.ColumnName, columnType));
            }
            return result;
        }

        #endregion

        #region BulkCopy

        /// <summary>
        /// Mssql批量录入
        /// </summary>
        /// <param name="entities">待录入实体</param>
        /// <returns>影响行</returns>
        /// <author>柯广强 2015.8.4</author>
        public int MssqlInserts<T>(List<T> entities)
        {
            var dataTable = this.EntityToDataTable(entities);

            try
            {
                OpenSharedConnection();
                try
                {
                    using (SqlBulkCopy sbc = new SqlBulkCopy(_sharedConnection as SqlConnection, SqlBulkCopyOptions.Default, _transaction as SqlTransaction))
                    {
                        for (int i = 0; i < dataTable.Columns.Count; i++)
                        {
                            sbc.ColumnMappings.Add(dataTable.Columns[i].ColumnName, dataTable.Columns[i].ColumnName);
                        }

                        sbc.DestinationTableName = dataTable.TableName;
                        sbc.BatchSize = entities.Count;
                        sbc.BulkCopyTimeout = 60;
                        sbc.WriteToServer(dataTable);
                        return sbc.BatchSize;
                    }
                }
                finally
                {
                    CloseSharedConnection();
                }
            }
            catch (Exception x)
            {
                if (OnException(x))
                    throw;
                return -1;
            }
        }


        /// <summary>
        /// Mysql批量录入
        /// </summary>
        /// <param name="entities">待录入实体</param>
        /// <returns>影响行</returns>
        /// <author>柯广强 2014.11.15</author>
        public int MysqlInserts<T>(List<T> entities)
        {
            var pd = PocoData.ForType(typeof(T));

            string s2 = "";
            foreach (var key in pd.Columns.Keys)
            {
                if (pd.Columns[key].ColumnName == pd.TableInfo.PrimaryKey
                    && pd.TableInfo.AutoIncrement
                    || pd.Columns[key].ResultColumn)
                    continue;

                s2 = s2 + "`" + pd.Columns[key].ColumnName + "`,";
            }

            StringBuilder insertSql = new StringBuilder(
                string.Format("INSERT INTO `{0}`({1}) VALUES ", pd.TableInfo.TableName, s2.TrimEnd(',')));

            foreach (T entity in entities)
            {
                insertSql.Append('(');
                //s2 = "(";
                foreach (var key in pd.Columns.Keys)
                {
                    if (pd.Columns[key].ColumnName == pd.TableInfo.PrimaryKey
                        && pd.TableInfo.AutoIncrement || pd.Columns[key].ResultColumn)
                        continue;


                    var value = pd.Columns[key].GetValue(entity);
                    if (value == null)
                        insertSql.Append("null,");
                    else
                    {
                        if (pd.Columns[key].PropertyInfo.PropertyType == typeof(bool))
                        {
                            bool b = Convert.ToBoolean(value);
                            insertSql.Append((b ? "1" : "0") + ",");
                        }
                        else if (pd.Columns[key].PropertyInfo.PropertyType == typeof(DateTime))
                        {
                            var time = Convert.ToDateTime(value);
                            insertSql.AppendFormat("'{0}',", time.ToString("yyyy-MM-dd HH:mm:ss"));
                        }
                        else if (pd.Columns[key].PropertyInfo.PropertyType == typeof(DateTime?))
                        {
                            if (value == null)
                            {
                                insertSql.Append("null,");
                            }
                            else
                            {
                                var time = Convert.ToDateTime(value);
                                insertSql.AppendFormat("'{0}',", time.ToString("yyyy-MM-dd HH:mm:ss"));
                            }
                        }
                        else if (value.GetType().BaseType.Name == "Enum")
                        {
                            insertSql.Append(value.GetHashCode() + ",");
                        }
                        else
                        {
                            insertSql.AppendFormat("'{0}',", Convert.ToString(value).Replace("'", "‘"));
                        }
                    }
                }
                insertSql.Remove(insertSql.Length - 1, 1);
                insertSql.Append("),");
            }
            insertSql.Remove(insertSql.Length - 1, 1);
            insertSql.Append(';');
            //return this.Execute(insertSql.ToString());

            return this.ExecuteNoParamete(insertSql.ToString());
        }

        #endregion

    }

    public partial class Transaction
    {
        public Database Db
        {
            get
            {
                return _db;
            }
        }
    }
}
